package com.qdlc.p2p.dal.dao.impl;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;

import org.springframework.stereotype.Service;

import com.qdlc.p2p.common.constant.Global;
import com.qdlc.p2p.dal.common.dao.jpa.BaseDaoImpl;
import com.qdlc.p2p.dal.dao.AwardSearchDao;
import com.qdlc.p2p.dal.dto.BorrowCollection;
import com.qdlc.p2p.dal.model.AwardModel;

/**
 * Created by lenovo on 2015/12/11.
 */

@Service("awardSearchDao")
public class AwardSearchDaoImpl extends BaseDaoImpl<BorrowCollection> implements AwardSearchDao{
    @Override
    public AwardModel firstTender(long borrowId) {
        String sql="SELECT qu.mobile_phone,bt.add_time,bt.money,qb.name,qu.user_id from " + Global.DB_PREFIX + "borrow_tender bt ," + Global.DB_PREFIX + "borrow qb ," + Global.DB_PREFIX + "user qu WHERE qb.id = bt.borrow_id  and qu.user_id = bt.user_id and  bt.borrow_id =:borrowId  AND qb.type = 112 AND qb.category = 1 AND qb.is_recommend = 0 AND bt.status in (0,1,3)  ORDER BY bt.add_time limit 1";
        Query query = em.createNativeQuery(sql).setParameter("borrowId", borrowId);
        List<Object[]> list = query.getResultList();
        if(!list.isEmpty()) {
            AwardModel awardModel = new AwardModel();
            for (Object[] obj : list) {
                awardModel.setMobile((obj[0] + "").substring(0, 3) + "****" + (obj[0] + "").substring((obj[0] + "").length()-4, (obj[0] + "").length()));
                awardModel.setAddtime(obj[1].toString().substring(0, 19));
                awardModel.setMoney(Double.parseDouble((obj[2] + "")));
                awardModel.setBorrowName(obj[3] + "");
                awardModel.setUserId(Long.parseLong(obj[4]+""));
            }

            return awardModel;
        }
        return null;
    }

    @Override
    public AwardModel lastTender(long borrowId) {
        String sql="SELECT qu.mobile_phone,bt.add_time,bt.money,qb.name,qu.user_id from " + Global.DB_PREFIX + "borrow_tender bt ," + Global.DB_PREFIX + "borrow qb ," + Global.DB_PREFIX + "user qu WHERE qb.id = bt.borrow_id  and qu.user_id = bt.user_id and  bt.borrow_id =:borrowId  AND qb.type = 112 AND qb.category = 1 AND qb.is_recommend = 0 AND bt.status in (0,1,3)  ORDER BY bt.add_time DESC limit 1";
        Query query = em.createNativeQuery(sql).setParameter("borrowId", borrowId);
        List<Object[]> list = query.getResultList();
        if(!list.isEmpty()) {
            AwardModel awardModel = new AwardModel();
            for (Object[] obj : list) {
                awardModel.setMobile((obj[0] + "").substring(0, 3) + "****" + (obj[0] + "").substring((obj[0] + "").length()-4, (obj[0] + "").length()));
                awardModel.setAddtime(obj[1].toString().substring(0, 19));
                awardModel.setMoney(Double.parseDouble((obj[2] + "")));
                awardModel.setBorrowName(obj[3] + "");
                awardModel.setUserId(Long.parseLong(obj[4]+""));
            }

            return awardModel;
        }
        return null;
    }

    @Override
    public AwardModel mostTender(long borrowId) {
        String sql="SELECT qu.mobile_phone, bt.add_time, sum(bt.money) AS total, qb.name,qu.user_id FROM " + Global.DB_PREFIX + "user qu, " + Global.DB_PREFIX + "borrow qb, " + Global.DB_PREFIX + "borrow_tender bt WHERE  qb.id = bt.borrow_id  and qu.user_id = bt.user_id and  bt.borrow_id =:borrowId  AND qb.type = 112 AND qb.category = 1 AND qb.is_recommend = 0 AND bt.status in (0,1,3) GROUP BY bt.user_id ORDER BY total DESC,add_time ASC LIMIT 1";
        Query query = em.createNativeQuery(sql).setParameter("borrowId", borrowId);
        List<Object[]> list = query.getResultList();
        if(!list.isEmpty()) {
            AwardModel awardModel = new AwardModel();
            for (Object[] obj : list) {
                awardModel.setMobile((obj[0] + "").substring(0, 3) + "****" + (obj[0] + "").substring((obj[0] + "").length()-4, (obj[0] + "").length()));
                awardModel.setAddtime(obj[1].toString().substring(0, 19));
                awardModel.setMoney(Double.parseDouble((obj[2] + "")));
                awardModel.setBorrowName(obj[3] + "");
                awardModel.setUserId(Long.parseLong(obj[4]+""));
            }
            return awardModel;
        }
        return null;
    }

    @Override
    public List<AwardModel> allFirstTender() {
        String idSql="select borrow_id from " + Global.DB_PREFIX + "borrow_tender GROUP BY borrow_id";
        Query idQuery = em.createNativeQuery(idSql);
        List<Integer> list = idQuery.getResultList();
        List<AwardModel> awards=new ArrayList<AwardModel>();
        for(Integer id:list){
            AwardModel awardModel= firstTender(id);
            if(awardModel!=null&&awardModel.getMoney()!=0)
                awards.add(awardModel);
        }
        return awards;
    }

    @Override
    public List<AwardModel> allLastTender() {
        String idSql="select borrow_id from " + Global.DB_PREFIX + "borrow_collection GROUP BY borrow_id";
        Query idQuery = em.createNativeQuery(idSql);
        List<Integer> list = idQuery.getResultList();
        List<AwardModel> awards=new ArrayList<AwardModel>();
        for(Integer id:list){
            AwardModel awardModel= lastTender(id);
            if(awardModel!=null&&awardModel.getMoney()!=0)
                awards.add(awardModel);
        }
        return awards;
    }

    @Override
    public List<AwardModel> allMostTender() {
        String idSql="select borrow_id from " + Global.DB_PREFIX + "borrow_collection GROUP BY borrow_id";
        Query idQuery = em.createNativeQuery(idSql);
        List<Integer> list = idQuery.getResultList();
        List<AwardModel> awards=new ArrayList<AwardModel>();
        for(Integer id:list){
            AwardModel awardModel= mostTender(id);
            if(awardModel!=null&&awardModel.getMoney()!=0)
                awards.add(awardModel);
        }
        return awards;
    }
}
